﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ZX.BLL.Extend
{
    public class SqlDBTable : DBTable
    {

        private readonly string const_AlterAddColSqlcomm =  "ALTER TABLE {0} ADD {1} {2}";
        private readonly string const_AlterAddColSqlcomm1 = "ALTER TABLE {0} ADD {1} {2}({3})";
        private readonly string const_CreateTableSqlcomm =  "Create Table {0} ({1})";

        private SqlDBTable()
        {
            this.tableField = new DBModule.SysTableField();
            this.entity = tableField;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="ColName"></param>
        /// <param name="ColType"></param>
        /// <returns></returns>
        public override bool AddCol(string TableName, string ColName, string ColType)
        {
            string sqlComm =  string.Format(const_AlterAddColSqlcomm,TableName,ColName,ColType);
            this.tableField.FieldName = ColName;
            this.tableField.FiledType = ColType.Split('(')[0];
            this.tableField.TableID = this.GetTableIDByAlaisName(TableName);
            this.tableField.Notes = "";
            this.RunSqlCommand(sqlComm);
            return true;
            //return base.AddCol(TableName, ColName, ColType);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="ColName"></param>
        /// <param name="ColType"></param>
        /// <param name="Length"></param>
        /// <returns></returns>
        public override bool AddCol(string TableName, string ColName, string ColType, int Length)
        {
            string sqlComm = string.Format(const_AlterAddColSqlcomm1, TableName, ColName, ColType,Length);
            this.RunSqlCommand(sqlComm);
            return true;
            //return base.AddCol(TableName, ColName, ColType, Length);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="FieldName"></param>
        /// <param name="Notes"></param>
        /// <returns></returns>
        public override bool AddColNotes(string TableName, string FieldName, string Notes) {
            string sqlComm = "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'"+Notes+"' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'"+TableName+"', @level2type=N'COLUMN',@level2name=N'"+FieldName+"'";
            this.RunSqlCommand(sqlComm);
            return false; 
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="FieldName"></param>
        /// <param name="MasterTableName"></param>
        /// <returns></returns>
        public override bool AlterForeignkey(string TableName, string FieldName, string MasterTableName)
        {
            string sqlComm = " ALTER TABLE " + TableName + " ADD CONSTRAINT FK_" + TableName + "_" + FieldName + " FOREIGN KEY (" + FieldName + ") REFERENCES " + MasterTableName + "(ID)";
            this.RunSqlCommand(sqlComm);
            return false;
        }
   
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="FieldList"></param>
        /// <returns></returns>
        public override bool CreateTable(string TableName, IList<DBModule.SysTableField> FieldList)
        {
            CreateTable(TableName);
            foreach (DBModule.SysTableField entity in FieldList)
            {
                AddCol(entity.GetTableName(), entity.FieldName, entity.FiledType);
            }
            return true;
            //return base.CreateTable(TableName, FieldList);
        }

        private void SaveTableField(string TableName, string ColName, string ColType, int Length)
        {
            DBModule.SysTableField entity = new DBModule.SysTableField(TableName, this.GetTableIDByTableName(TableName));
        }

        public override bool CreateTable(string TableName)
        {
            StringBuilder strSql = new StringBuilder("Create Table [" + TableName + "] (\n\r");
            strSql.Append("ID uniqueidentifier not null,PRIMARY KEY(ID))");
            //"ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_ID]  DEFAULT (newid()) FOR [ID]"
            this.RunSqlCommand(strSql.ToString());
            return true;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public override bool IsTableExists(string TableName) 
        {
            bool isExits = false;


            SqlParameter[] sqlparams = new SqlParameter[]{
                new SqlParameter("@TableName",SqlDbType.VarChar,200)
             };
      
            sqlparams[0].Value = TableName;
           
            DataSet ds = this.RunProcedure("sp_Set_Table_GetIsExists", sqlparams);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0) { isExits = true; }
                else { isExits = false; }
            }
            return isExits;
        }

        public static DBTable Instance = new SqlDBTable();
    }

    public class SqliteDBTable : DBTable
    {
    }

    public class OracelDBTable : DBTable
    {
    }
}
